## 10.2. An Example DBI Application

DBI allows for the full range of SQL queries supported by MySQL and mSQL. As an example, consider a database used by a school to keep track of student records, class schedules, test scores, and so on. The database would contain several tables, one for class information, one for student information, one containing a list of tests, and a table for each test. MySQL and mSQL's ability to access data across tables -- such as the table-joining feature -- enables all of these tables to be used together as a coherent whole to form a teacher's aide application.

To begin with we are interested in creating tests for the various subjects. To do this we need a table that contains names and ID numbers for the tests. We also need a separate table for each test. This table will contain the scores for all of the students as well as a perfect score for comparison. The `test` table has the following structure:

>     CREATE TABLE test (
>       id INT NOT NULL AUTO_INCREMENT,
>       name CHAR(100),
>       subject INT,
>       num INT
>     )

The individual tests have table structures like this:

>     CREATE TABLE t7 (
>       id INT NOT NULL,
>       q1 INT,
>       q2 INT,
>       q3 INT,
>       q4 INT,
>       total INT
>     )

The table name is `t` followed by the test ID number from the `test` table. The user determines the number of questions when he or she creates the table. The total field is the sum of all of the questions.

The program that accesses and manipulates the test information is test.cgi. This program, which follows, allows only for adding new tests. Viewing tests and changing tests is not implemented but is left as an exercise. Using the other scripts in this chapter as a reference, completing this script should be only a moderate challenge. As it stands, this script effectively demonstrates the capabilities of DBI:[17]

> [17]This example is a MySQL example. Of course, the API is identical for mSQL. The only "glitch" is with sequence generation. Remember that where MySQL automatically generates the next ID for the `test` table because of the `AUTO_INCREMENT` keyword, mSQL expects you to create a sequence on the `test` table and `SELECT` the `_seq` value before doing your insert.

>     #!/usr/bin/perl -w

>     use strict;
>     require my_end;

>     use CGI qw(:standard);
>     my $output = new CGI;
>     use_named_parameters(1);

>     # Use the DBI module.
>     use DBI;
>     # DBI::connect() uses the format 'DBI:driver:database', in our case we are
>     # using the MySQL driver and accessing the 'teach' database.
>     my $dbh = DBI->connect('DBI:mysql:teach');
>     # The add action itself is broken up into three separate functions. The first # function, add, prints out the template form for the user to create a new # # test.
>     sub add {
>        $subject = param('subject') if (param('subjects'));
>        $subject = "" if $subject eq 'all';

>        print header, start_html('title'=>'Create a New Test',
>           'BGCOLOR'=>'white');
>        print <<END_OF_HTML;
>     <H1>Create a New Test</h1>
>     <FORM ACTION="test.cgi" METHOD=POST>
>     <INPUT TYPE=HIDDEN NAME="action" VALUE="add2">
>     Subject:
>     END_OF_HTML
>        my @ids = ();
>        my %subjects = ();
>        my $out2 = $dbh->prepare("select id,name from subject order by name");
>        $out2->execute;
>        # DBI::fetchrow_array() is exactly analogous to Msql::fetchrow()
>        while(my($id,$subject)=$out2->fetchrow_array) {
>           push(@ids,$id);
>           $subjects{"$id"} = $subject;
>        }
>        print popup_menu('name'=>'subjects',
>           'values'=>[@ids],
>           'default'=>$subject,
>           'labels'=>\%subjects);
>        print <<END_OF_HTML;
>     <br>
>     Number of Questions: <INPUT NAME="num" SIZE=5><br>
>     A name other identifier (such as a date) for the test:
>      <INPUT NAME="name" SIZE=20>
>     <p>
>     <INPUT TYPE=SUBMIT VALUE=" Next Page ">
>      <INPUT TYPE=RESET>
>     </form></body></html>
>     END_OF_HTML
>     }

This function displays a form allowing the user to choose a subject for the test along with the number of questions and a name. In order to print out a list of available subjects, the table of subjects is queried. When using a `SELECT` query with DBI, the query must first be prepared and then executed. The `DBI::prepare` function is useful with certain database servers which allow you to perform operations on prepared queries before executing them. With MySQL and mSQL however, it simply stores the query until the `DBI::execute` function is called.

The output of this function is sent to the `add2` function as shown in the following:

>     sub add2 {
>        my $subject = param('subjects');
>        my $num = param('num');
>        $name = param('name') if param('name');

>        my $out = $dbh->prepare("select name from subject where id=$subject");
>        $out->execute;
>        my ($subname) = $out->fetchrow_array;

>        print header, start_html('title'=>"Creating test for $subname",
>           'BGCOLOR'=>'white');
>        print <<END_OF_HTML;
>     <H1>Creating test for $subname</h1>
>     <h2>$name</h2>
>     <p>
>     <FORM ACTION="test.cgi" METHOD=POST>
>     <INPUT TYPE=HIDDEN NAME="action" VALUE="add3">
>     <INPUT TYPE=HIDDEN NAME="subjects" VALUE="$subject">
>     <INPUT TYPE=HIDDEN NAME="num" VALUE="$num">
>     <INPUT TYPE=HIDDEN NAME="name" VALUE="$name">
>     Enter the point value for each of the questions. The points need not
>     add up to 100.
>     <p>
>     END_OF_HTML
>        for (1..$num) {
>           print qq%$_: <INPUT NAME="q$_" SIZE=3> %;
>           if (not $_ % 5) { print "<br>\n"; }
>        }
>        print <<END_OF_HTML;
>     <p>
>     Enter the text of the test:<br>
>     <TEXTAREA NAME="test" ROWS=20 COLS=60>
>     </textarea>
>     <p>
>     <INPUT TYPE=SUBMIT VALUE="Enter Test">
>      <INPUT TYPE=RESET>
>     </form></body></html>
>     END_OF_HTML
>     }

In this function, a form for the test is dynamically generated based on the parameters entered in the last form. The user can enter the point value for each question on the test and the full text of the test as well. The output of this function is then sent to the final function, `add3`, as shown in the following:

>     sub add3 {
>        my $subject = param('subjects');
>        my $num = param('num');

>        $name = param('name') if param('name');

>        my $qname;
>        ($qname = $name) =~ s/'/\\'/g;
>        my $q1 = "insert into test (id, name, subject, num) values (
>           '', '$qname', $subject, $num)";

>        my $in = $dbh->prepare($q1);
>        $in->execute;

>        # Retrieve the ID value MySQL created for us
>        my $id = $in->insertid;

>        my $query = "create table t$id (
>           id INT NOT NULL,
>           ";

>        my $def = "insert into t$id values ( 0, ";

>        my $total = 0;
>        my @qs = grep(/^q\d+$/,param);
>        foreach (@qs) {
>           $query .= $_ . " INT,\n";
>           my $value = 0;
>           $value = param($_) if param($_);
>           $def .= "$value, ";
>           $total += $value;
>        }
>        $query .= "total INT\n)";
>        $def .= "$total)";

>        my $in2 = $dbh->prepare($query);
>        $in2->execute;
>        my $in3 = $dbh->prepare($def);
>        $in3->execute;

>        # Note that we store the tests in separate files. This is
>        # useful when dealing with mSQL because of its lack of BLOBs.
>        # (The TEXT type provided with mSQL 2 would work, but
>        # inefficently.)
>        # Since we are using MySQL, we could just as well
>        # stick the entire test into a BLOB.
>        open(TEST,">teach/tests/$id") or die("A: $id $!");
>        print TEST param('test'), "\n";
>        close TEST;

>        print header, start_html('title'=>'Test Created',
>          'BGCOLOR'=>'white');
>        print <<END_OF_HTML;
>     <H1>Test Created</h1>
>     <p>
>     The test has been created.
>     <p>
>     <A HREF=".">Go</a> to the Teacher's Aide home page.<br>
>     <A HREF="test.cgi">Go</a> to the Test main page.<br>
>     <A HREF="test.cgi?action=add">Add</a> another test.
>     </body></html>
>     END_OF_HTML
>     }

Here we enter the information about the test into the database. In doing so we take a step beyond the usual data insertion that we have seen so far. The information about the test is so complex that each test is best kept in a table of its own. Therefore, instead of adding data to an existing table, we have to create a whole new table for each test. First we create an ID for the new test using MySQL auto increment feature and enter the name and ID of the test into a table called `test`. This table is simply an index of tests so that the ID number of any test can be quickly obtained. Then we simultaneously create two new queries. The first is a `CREATE TABLE` query which defines our new test. The second is an `INSERT` query that populates our table with the maximum score for each question. These queries are then sent to the database server, completing the process (after sending a success page to the user). Later, after the students have taken the test, each student will get an entry in the test table. Then entries can then be compared to the maximum values to determine the student's score.

Copyright © 2001 O'Reilly & Associates. All rights reserved.